with wide_unsign as (
    select waybill_no
         , send_station_loading_later_time                  --A发件中心晚装车时间
         , send_station_send_later_time                     --A发件中心晚发件时间
         , send_station_unloading_early_time                --A路段始发站点最早卸车时间
         , send_station_arrival_early_time                  --A路段始发站点最在到件时间
         , next_station_unloading_early_time                --B下一站最早卸车时间
         , next_station_arrival_early_time                  --B下一站最早到车时间
         , next_station_loading_later_time                  --B下一站最晚装车时间
         , next_station_send_later_time                     --B下一站最晚发件时间
         , if(taking_early_scantime is not null
                  and
              (
                      (
                              send_station_loading_later_time is null
                              and send_station_send_later_time is null
                              and send_station_unloading_early_time is null
                              and send_station_arrival_early_time is null
                          ) or
                      (
                                  unix_timestamp(least(send_station_loading_later_time, send_station_send_later_time,
                                                       send_station_unloading_early_time,
                                                       send_station_arrival_early_time)) -
                                  unix_timestamp(taking_early_scantime) > 48 * 60 * 60
                          )
                  ), 1, 0)     as have_taking_no_send_flag  --有揽未发标识,1代表有揽未发(48小时内没有后续的轨迹信息)
         , if(taking_early_scantime is not null
                  and taking_early_scansitecode = first_network_network_code
                  and send_station_network_code = taking_early_scansitecode
                  and send_station_send_later_time is not null
                  and (
                      (
                              next_station_unloading_early_time is null
                              and next_station_arrival_early_time is null
                              and next_station_loading_later_time is null
                              and next_station_send_later_time is null
                          ) or
                      (
                                  unix_timestamp(
                                          least(next_station_unloading_early_time, next_station_arrival_early_time,
                                                next_station_loading_later_time,
                                                next_station_send_later_time)) -
                                  unix_timestamp(send_station_send_later_time) > 48 * 60 * 60
                          )
                  ), 1, 0)     as have_send_no_arrival_flag --有发未到标识,1代表有发未到(规定时间内48小时内)
         , to_date(input_time) as date_time
    from jms_dwd.dwd_wide_unsign_trace_waybill_step_dt
    where dt >= date_add('{{ execution_date | cst_ds }}', -44)
      and dt <= '{{ execution_date | cst_ds }}'
      and to_date(input_time) >= date_add('{{ execution_date | cst_ds }}', -6)
      and to_date(input_time) <= '{{ execution_date | cst_ds }}'
      and customer_code is not null
)
insert
overwrite
table
jms_dm.dm_mng_customer_daliy_waybill_tab_detail_dt
partition
(
dt
)
select t1.project_name
     , t1.id
     , t1.code
     , t1.name
     , t1.network_id
     , t1.waybill_no
     , t1.order_status_code
     , t1.input_time
     , t1.pick_time
     , t1.real_pick_network_code
     , t1.dispatch_network_code
     , t1.destination_id
     , t1.cancel_flag
     , t1.pick_flag
     , t1.dt as order_date_time
     , t1.is_sign
     , t1.last_update_time
     , t1.way_input_time
     , t1.sign_falg
     , t1.7d_update_flag
     , t1.actual_taking_join
     , t1.del_war_flag
     , t1.del_war_join
     , t1.intercept_flag
     , t1.intercept_join
     , t1.difficult_flag
     , t1.difficute_join
     , t1.work_order_flag
     , t1.work_order_join
     , t1.arbitration_flag
     , t1.arbitration_join
     , t1.express_flag
     , t1.express_join
     , t1.xujia_sign_flag
     , t1.xujia_sign_join
     , t1.cancel_time
     , t1.order_id
     , t1.real_pick_network_name
     , t1.sign_network_code
     , t1.sign_network_name
     , t1.sign_time
     , t1.problem_type_id
     , t1.problem_type_code
     , t1.problem_type_name
     , t1.problem_type_subject_id
     , t1.problem_type_subject_code
     , t1.problem_type_subject_name
     , t1.problem_description
     , t1.first_type_code
     , t1.first_type_name
     , t1.second_type_code
     , t1.second_type_name
     , t1.dispatch_time
     , t1.diff_3input_time_flag
     , t1.diff_10input_time_flag
     , t1.wph_pro_name_flag
     , t1.pick_time_flag
     , t1.sender_province_id
     , t1.sender_province_name
     , t1.sender_city_id
     , t1.sender_city_name
     , t1.sender_area_id
     , t1.sender_area_name
     , t1.receiver_province_id
     , t1.receiver_province_name
     , t1.receiver_city_id
     , t1.receiver_city_name
     , t1.receiver_area_id
     , t1.receiver_area_name
     , t1.in_store_scan_time
     , t1.start_time
     , t1.end_time
     , t1.diff_day
     , t1.pp_city_t_timely
     , t1.pc_city_t_timely
     , t1.cp_city_t_timely
     , t1.cc_city_t_timely
     , t1.need_city_t_timely
     , t1.dispatch_network_name
     , t1.taking_early_scantime
     , t1.taking_early_scanday
     , t1.on_taking_flag
     , t1.mall_id
     , t1.mall_name
     , t1.customer_network_id
     , t1.customer_network_code
     , t1.customer_network_name
     , t2.send_station_loading_later_time
     , t2.send_station_send_later_time
     , t2.send_station_unloading_early_time
     , t2.send_station_arrival_early_time
     , t2.next_station_unloading_early_time
     , t2.next_station_arrival_early_time
     , t2.next_station_loading_later_time
     , t2.next_station_send_later_time
     , t2.have_taking_no_send_flag  --有揽未发标识,1代表有揽未发(48小时内没有后续的轨迹信息)
     , t2.have_send_no_arrival_flag --有发未到标识,1代表有发未到(规定时间内48小时内)
     , t1.dt as date_time
     , t1.arb_first_type_code
     , t1.arb_first_type
     , t1.arb_second_type_code
     , t1.arb_second_type
     , t1.customer_agent_code
     , t1.customer_agent_name
     , t1.comp_create_time    --客诉登记时间
     , t1.arb_create_time     --仲裁申报时间
     , t1.problem_create_time --质量申报时间
     , t1.input_time_6hour    --订单录入加6日期
     , t1.dt as dt
from (
         select *
         from jms_dm.dm_mng_customer_daliy_waybill_detail_dt
         where dt >= date_add('{{ execution_date | cst_ds }}', -6)
           and dt <= '{{ execution_date | cst_ds }}'
           and substr(way_input_time, 1, 10) >= date_add('{{ execution_date | cst_ds }}', -6)
           and substr(way_input_time, 1, 10) <= '{{ execution_date | cst_ds }}'
           and input_time is not null
     ) t1
         join
     (
         select waybill_no
              , null                           as send_station_loading_later_time   --A发件中心晚装车时间
              , null                           as send_station_send_later_time      --A发件中心晚发件时间
              , null                           as send_station_unloading_early_time --A路段始发站点最早卸车时间
              , null                           as send_station_arrival_early_time   --A路段始发站点最在到件时间
              , null                           as next_station_unloading_early_time --B下一站最早卸车时间
              , null                           as next_station_arrival_early_time   --B下一站最早到车时间
              , null                           as next_station_loading_later_time   --B下一站最晚装车时间
              , null                           as next_station_send_later_time      --B下一站最晚发件时间
              , min(have_taking_no_send_flag)  as have_taking_no_send_flag          --有揽未发标识,1代表有揽未发(48小时内没有后续的轨迹信息)
              , min(have_send_no_arrival_flag) as have_send_no_arrival_flag         --有发未到标识,1代表有发未到(规定时间内48小时内)
         from wide_unsign
         group by waybill_no
         having (have_send_no_arrival_flag = 1
             or have_taking_no_send_flag = 1)
     ) t2
     on t1.waybill_no = t2.waybill_no
    distribute by dt
;
